<?php
    require_once("classes/Database.php");
    $ficheiro = 'installed.token';
    
	$db = new Database("localhost", "root", "");
	
    if(!file_exists($ficheiro)){
        if(!$db->executeQuery("CREATE DATABASE emprego")){
            echo 'Erro a Criar a Base de Dados: ' . mysql_error();
        }
    
        $db->connect("emprego");
        
        $sql = "CREATE TABLE users (  
            id  BIGINT(20) NOT NULL AUTO_INCREMENT,  
            nome    VARCHAR(254) NOT NULL,  
            username    VARCHAR(45) NOT NULL, 
            password    VARCHAR(45) NOT NULL,
            update_date DATE NOT NULL,
            save_date   DATE NOT NULL,
            email   VARCHAR(254) NOT NULL,
            roles   TEXT NOT NULL,
            is_super    TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,  
            PRIMARY KEY (id)  
        ) ENGINE=INNODB";  
        $db->executeQuery($sql);
    	
        $sql = "CREATE TABLE category (
            id  BIGINT(20) NOT NULL AUTO_INCREMENT,
            name    VARCHAR(90) NOT NULL,
            description TEXT,
            PRIMARY KEY (id)
        ) ENGINE=INNODB";
        $db->executeQuery($sql);
        
        $sql = "CREATE TABLE location (
            id  BIGINT(20) NOT NULL AUTO_INCREMENT,
            name    VARCHAR(90) NOT NULL,
            PRIMARY KEY (id)
        ) ENGINE=INNODB";
        $db->executeQuery($sql);
        
        $sql = "CREATE TABLE curriculum (  
            id  BIGINT(20) NOT NULL,    
            category    BIGINT(20) NOT NULL,
            description TEXT,
            PRIMARY KEY (id),
            FOREIGN KEY(id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(category) REFERENCES category(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=INNODB";  
        $db->executeQuery($sql);
        
        $sql = "CREATE TABLE qualifications (
            id  BIGINT(20) NOT NULL AUTO_INCREMENT,
            curriculum  BIGINT(20) NOT NULL,
            school  VARCHAR(90) NOT NULL,
            curse   VARCHAR(90) NOT NULL,
            start_date  DATE NOT NULL,
            end_date    DATE NOT NULL,
            PRIMARY KEY (id),
            FOREIGN KEY(curriculum) REFERENCES curriculum(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=INNODB";
        $db->executeQuery($sql);
        
        $sql = "CREATE TABLE experience (
            id  BIGINT(20) NOT NULL AUTO_INCREMENT,
            curriculum  BIGINT(20) NOT NULL,
            enterprise  VARCHAR(90) NOT NULL,
            activity    VARCHAR(255) NOT NULL,
            start_date  DATE NOT NULL,
            end_date    DATE NOT NULL,
            PRIMARY KEY (id),
            FOREIGN KEY(curriculum) REFERENCES curriculum(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=INNODB";
        $db->executeQuery($sql);
        
        $sql = "CREATE TABLE announcement (
            id  BIGINT(20) NOT NULL AUTO_INCREMENT,
            owner   BIGINT(20) NOT NULL,
            title   VARCHAR(90) NOT NULL,
            description TEXT NOT NULL,
            location    BIGINT(20) NOT NULL,
            category    BIGINT(20) NOT NULL,
            salary  DECIMAL,
            update_date DATE NOT NULL,
            savedate    DATE NOT NULL,
            PRIMARY KEY (id),
            FOREIGN KEY(owner) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(location) REFERENCES location(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(category) REFERENCES category(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=INNODB";
        $db->executeQuery($sql);
        
        $sql = "CREATE TABLE posts (
            id  BIGINT(20)  NOT NULL AUTO_INCREMENT,
            announcement    BIGINT(20) NOT NULL,
            candidate   BIGINT(20) NOT NULL,
            content TEXT NOT NULL,
            update_date DATE NOT NULL,
            save_date   DATE NOT NULL,
            PRIMARY KEY (id),
            FOREIGN KEY(announcement) REFERENCES announcement(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(candidate) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=INNODB";
        $db->executeQuery($sql);
        
        $ficheiro = fopen("installed.token", "a");
    
        fclose($ficheiro);
    
        $db->disconnect();
    }
?>